﻿using System;
using System.IO;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Web;
using System.Web.Security;
using System.Security.Cryptography;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
using Jungle.Code.DAL;

namespace Jungle.Prodamo.AdvertManager
{
    public class Advert
    {
        private int advertId;
        private int userId;
        private string title;
        private string body;
        private DateTime pubDate;
        private DateTime endDate;
        private int type; // 0 - продажа, 1 - покупка, 2 - обмен
        private bool premiumType;
        private double price;
        private int currency; // 0 - гривна, 1 - евро, 2 - доллар
        private string imageUrl;

        public List<Advert> AdvertList;

        public int AdvertId { get { return advertId; } set { advertId = value; } }
        public int UserId { get { return userId; } set { userId = value; } }
        public string Title { get { return title; } set { title = value; } }
        public string Body { get { return body; } set { body = value; } }
        public DateTime PubDate { get { return pubDate; } set { pubDate = value; } }
        public DateTime EndDate { get { return endDate; } set { endDate = value; } }
        public int Type { get { return type; } set { type = value; } }
        public bool PremiumType { get { return premiumType; } set { premiumType = value; } }
        public double Price { get { return price; } set { price = value; } }
        public int Currency { get { return currency; } set { currency = value; } }
        public string ImageUrl { get { return imageUrl; } set { imageUrl = value; } }

        public Advert()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Adverts_DeleteOlds", connect);
                command.CommandType = CommandType.StoredProcedure;
                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
        }

        public void Add()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Adverts_Add", connect);
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
                command.Parameters.Add("@Title", SqlDbType.NChar, 255).Value = title;
                command.Parameters.Add("@Body", SqlDbType.Text).Value = body;
                command.Parameters.Add("@Type", SqlDbType.Int).Value = type;
                command.Parameters.Add("@PremiumType", SqlDbType.Bit).Value = premiumType;
                command.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endDate;
                command.Parameters.Add("@Price", SqlDbType.Float).Value = price;
                command.Parameters.Add("@Currency", SqlDbType.Int).Value = currency;
                command.Parameters.Add("@ImageUrl", SqlDbType.NChar, 255).Value = imageUrl;

                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    advertId = Convert.ToInt32(reader.GetValue(0));
                }
                reader.Close();
                command.Connection.Close();
            }
        }
        public void Delete()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Adverts_Delete", connect);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@AdvertId", SqlDbType.Int).Value = advertId;

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }        
        }
        public void Update()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Adverts_Update", connect);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@AdvertId", SqlDbType.Int).Value = advertId;
                command.Parameters.Add("@Title", SqlDbType.NChar, 255).Value = title;
                command.Parameters.Add("@Body", SqlDbType.Text).Value = body;
                command.Parameters.Add("@Type", SqlDbType.Int).Value = type;
                command.Parameters.Add("@PremiumType", SqlDbType.Bit).Value = premiumType;
                command.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endDate;
                command.Parameters.Add("@Price", SqlDbType.Float).Value = price;
                command.Parameters.Add("@Currency", SqlDbType.Int).Value = currency;
                command.Parameters.Add("@ImageUrl", SqlDbType.NChar, 255).Value = imageUrl;

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }            
        }
        public void GetAll()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Adverts_GetAll", connect);
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;

                AdvertList = new List<Advert>();
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Advert adv = new Advert();
                    adv.advertId = (int)reader["AdvertId"];
                    adv.GetById();

                    AdvertList.Add(adv);
                }
                reader.Close();
                command.Connection.Close();
            }            
        }
        public void GetAllPremiums()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Adverts_GetAllPremiums", connect);
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;

                AdvertList = new List<Advert>();
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Advert adv = new Advert();
                    adv.advertId = (int)reader["AdvertId"];
                    adv.GetById();

                    AdvertList.Add(adv);
                }
                reader.Close();
                command.Connection.Close();
            }
        }
        public void GetById()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Adverts_GetById", connect);
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@AdvertId", SqlDbType.Int).Value = advertId;
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    advertId = (int)reader["AdvertId"];
                    userId = (int)reader["UserId"];
                    title = reader["Title"].ToString().Trim();
                    body = reader["Body"].ToString().Trim();
                    pubDate = (DateTime)reader["PubDate"];
                    type = (int)reader["Type"];
                    premiumType = (bool)reader["PremiumType"];
                    endDate = (DateTime)reader["EndDate"];
                    price = (double)reader["Price"];
                    currency = (int)reader["Currency"];
                    imageUrl = reader["ImageUrl"].ToString().Trim();
                }
                reader.Close();
                command.Connection.Close();
            }
        }
        public void GetByUser()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Adverts_GetByUser", connect);
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;

                AdvertList = new List<Advert>();
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Advert adv = new Advert();
                    adv.advertId = (int)reader["AdvertId"];
                    adv.GetById();

                    AdvertList.Add(adv);
                }
                reader.Close();
                command.Connection.Close();
            }
        }

    }
    public class Rubrik
    {
        private int rubrikId;
        private string title;
        private int parentId;

        public List<Rubrik> RubrikList;

        public int RubrikId { get { return rubrikId; } set { rubrikId = value; } }
        public string Title { get { return title; } set { title = value; } }
        public int ParentId { get { return parentId; } set { parentId = value; } }

        public void Add()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Rubriks_Add", connect);
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@Title", SqlDbType.NChar, 255).Value = title;
                command.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId;

                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    rubrikId = Convert.ToInt32(reader.GetValue(0));
                }
                reader.Close();
                command.Connection.Close();
            }            
        }
        public void GetAll()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Rubriks_GetAll", connect);
                SqlDataReader reader = null;

                RubrikList = new List<Rubrik>();
                command.CommandType = CommandType.StoredProcedure;
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Rubrik rb = new Rubrik();
                    rb.rubrikId = (int)reader["RubrikId"];
                    rb.title = reader["Title"].ToString().Trim();
                    rb.parentId = (int)reader["ParentId"];

                    RubrikList.Add(rb);
                }
                reader.Close();
                command.Connection.Close();
            }
        }
        public void GetById()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Rubriks_GetById", connect);
                SqlDataReader reader = null;

                command.Parameters.Add("@RubrikId", SqlDbType.Int).Value = rubrikId;
                command.CommandType = CommandType.StoredProcedure;
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    rubrikId = (int)reader["RubrikId"];
                    title = reader["Title"].ToString().Trim();
                    parentId = (int)reader["ParentId"];
                }
                reader.Close();
                command.Connection.Close();
            }        
        }
        public void GetByParentId()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Rubriks_GetByParentId", connect);
                SqlDataReader reader = null;

                RubrikList = new List<Rubrik>();
                command.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId;
                command.CommandType = CommandType.StoredProcedure;
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Rubrik rb = new Rubrik();
                    rb.RubrikId = (int)reader["RubrikId"];
                    rb.GetById();

                    RubrikList.Add(rb);

                }
                reader.Close();
                command.Connection.Close();
            }
        }


    }
    public class ABR
    {
        private int advertId;
        private int rubrikId;

        public List<Advert> AdvertList;
        public List<Rubrik> RubrikList;

        public int AdvertId { get { return advertId; } set { advertId = value; } }
        public int RubrikId { get { return rubrikId; } set { rubrikId = value; } }

        public void Add()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("AdvertsByRubriks_Add", connect);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@AdvertId", SqlDbType.Int).Value = advertId;
                command.Parameters.Add("@RubrikId", SqlDbType.Int).Value = rubrikId;

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
        }
        public void GetByRubrik()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("AdvertsByRubriks_GetByRubrik", connect);
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@RubrikId", SqlDbType.Int).Value = rubrikId;

                AdvertList = new List<Advert>();
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Advert adv = new Advert();
                    adv.AdvertId = (int)reader["AdvertId"];
                    adv.GetById();

                    AdvertList.Add(adv);
                }
                reader.Close();
                command.Connection.Close();
            }            
        }
        public void GetByAdvert()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("AdvertsByRubriks_GetByAdvert", connect);
                SqlDataReader reader = null;
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add("@AdvertId", SqlDbType.Int).Value = advertId;

                RubrikList = new List<Rubrik>();
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Rubrik rb = new Rubrik();
                    rb.RubrikId = (int)reader["RubrikId"];
                    rb.GetById();

                    RubrikList.Add(rb);
                }
                reader.Close();
                command.Connection.Close();
            }
        }
    }
}